MyBatis Mapper练习

MyBatisTest

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
package test;

import cn.xiaojia521.bean.Employee;
import cn.xiaojia521.dao.EmployeeMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author xiaojia
 * @date 2021/5/11 17:18
 */
public class MyBatisTest {

    public SqlSessionFactory getSqlsessionFactory() throws IOException {
        String resource="mybatis-config.xml";
        InputStream inputStream= Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }

    @Test
    public void test1() throws IOException {
        SqlSessionFactory sqlsessionFactory = getSqlsessionFactory();
        try (SqlSession sqlSession = sqlsessionFactory.openSession()) {
            Employee emp = sqlSession.selectOne("cn.xiaojia521.dao.getEmpById", 1);
            System.out.println(emp);
        }
    }

    @Test
    public void test2() throws IOException {
        SqlSessionFactory session = getSqlsessionFactory();
        SqlSession sqlSession = session.openSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        Employee emp = mapper.getEmpById(1);
        System.out.println(emp);
    }

    @Test
    public void test3() throws IOException{
        SqlSessionFactory sqlsessionFactory = getSqlsessionFactory();
        try (SqlSession session = sqlsessionFactory.openSession()) {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
//            Employee employee = new Employee();
//            employee.setEmail("[email protected]");
//            employee.setGender("1");
//            employee.setLast_name("jerry");
////            employee.setId(2);
//            mapper.addEmp(employee);
////            mapper.updateEmp(employee);
////            mapper.deleteEmp(2);
//            session.commit();
////            System.out.println(employee.getId());
//            Employee tom = mapper.getEmpByIdAndLastName(1, "tom");
//            System.out.println(tom);

            Map<String, Object> map = new HashMap<>();
            map.put("id", 1);
            map.put("last_name", "tom");
            Employee empByMap = mapper.getEmpByMap(map);
            System.out.println(empByMap);

        }

    }

    @Test
    public void test4() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sessionFactory.openSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        mapper.deleteEmp(1);
    }

    @Test
    public void test5() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        List<Employee> emps = mapper.getEmpsByLastNameLike("%e%");
        for (Employee emp:
             emps) {
            System.out.println(emp);
        }
    }

    @Test
    public void test6() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        Map<String, Object> empByIdReturnMap = mapper.getEmpByIdReturnMap(3);
        System.out.println(empByIdReturnMap);
    }

    @Test
    public void test7() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
        Map<String, Employee> empByLastNameLikeReturnMap = mapper.getEmpByLastNameLikeReturnMap("%e%");
        System.out.println(empByLastNameLikeReturnMap);
    }
}

mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <properties resource="dbconfig.properties"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.passwd}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="EmployeeMapper.xml"/>
    </mappers>
</configuration>

EmployeeMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.xiaojia521.dao.EmployeeMapper">

    <select id="getEmpByLastNameLikeReturnMap" resultType="cn.xiaojia521.bean.Employee">
        select * from tbl_employee where last_name like #{last_name}
    </select>

    <select id="getEmpByIdReturnMap" resultType="map">
        select *from tbl_employee where id=#{id}
    </select>

    <select id="getEmpsByLastNameLike" resultType="cn.xiaojia521.bean.Employee">
        select * from tbl_employee where last_name like #{last_name}
    </select>

    <select id="getEmpByMap" resultType="cn.xiaojia521.bean.Employee">
        select * from tbl_employee where id=#{id} and last_name=#{last_name}
    </select>

    <select id="getEmpByIdAndLastName" resultType="cn.xiaojia521.bean.Employee">
        select * from tbl_employee where id=#{id} and last_name=#{last_name}
    </select>

    <select id="getEmpById" resultType="cn.xiaojia521.bean.Employee">
        select * from tbl_employee where id = #{id}
    </select>

    <insert id="addEmp" parameterType="cn.xiaojia521.bean.Employee" useGeneratedKeys="true" keyProperty="id">
        insert into tbl_employee(last_name,email,gender) values(#{last_name},#{email},#{gender})
    </insert>

    <update id="updateEmp" parameterType="cn.xiaojia521.bean.Employee">
        update tbl_employee set last_name=#{last_name},email=#{email},gender=#{gender} where id=#{id}
    </update>

<!--    <delete id="deleteEmp" parameterType="cn.xiaojia521.bean.Employee">-->
<!--        delete from tbl_employee where id=#{id}-->
<!--    </delete>-->

    <delete id="deleteEmp" parameterType="cn.xiaojia521.bean.Employee">
        delete from tbl_employee where id=#{id}
    </delete>
</mapper>

EmployeeMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package cn.xiaojia521.dao;

import cn.xiaojia521.bean.Employee;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * @author xiaojia
 * @date 2021/5/11 18:21
 */
public interface EmployeeMapper {

    @MapKey("last_name")
    Map<String,Employee> getEmpByLastNameLikeReturnMap(String lastName);

    Map<String,Object> getEmpByIdReturnMap(int id);

    List<Employee> getEmpsByLastNameLike(String lastName);

    Employee getEmpByMap(Map<String,Object> map);

    Employee getEmpByIdAndLastName(@Param("id") int id, @Param("last_name") String last_name);

    Employee getEmpById(int id);

    void addEmp(Employee employee);

    void updateEmp(Employee employee);

    void deleteEmp(int id);

}